{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# コンテナリスト"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## コンテナ設定リスト\n",
    "\n",
    "|コンテナ|サービス|VIP|port|\n",
    "|---|---|---|---|\n",
    "|mysql5.6|mysql|172.17.33.11|3306|\n",
    "|mysql5.6|ssh|172.17.33.11|2222|\n",
    "|openldap|ldap|172.17.33.12|389|\n",
    "|openldap|ssh|172.17.33.12|2223|\n",
    "|mail_server|imap|172.17.33.13|1143|\n",
    "|mail_server|smtp|172.17.33.13|2525|\n",
    "|mail_server|ssh|172.17.33.13|2224|\n",
    "|mail_server_virtual_for joruri|imap|172.17.33.14|1144|\n",
    "|mail_server_virtual_for joruri|smtp|172.17.33.14|2526|\n",
    "|mail_server_virtual_for joruri|ssh|172.17.33.14|2224|"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 現在のステータス"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### イメージリスト"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 142,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>REPOSITORY</th>\n",
       "      <th>TAG</th>\n",
       "      <th>IMAGE ID</th>\n",
       "      <th>CREATED</th>\n",
       "      <th>SIZE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ftakao2007/mysql5.6_centos7</td>\n",
       "      <td>latest</td>\n",
       "      <td>09a773ad8e34</td>\n",
       "      <td>12 days ago</td>\n",
       "      <td>2.16GB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ftakao2007/openldap_centos7</td>\n",
       "      <td>latest</td>\n",
       "      <td>572b38993fa4</td>\n",
       "      <td>10 days ago</td>\n",
       "      <td>596MB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ftakao2007/mail_centos7</td>\n",
       "      <td>latest</td>\n",
       "      <td>0733ee002c86</td>\n",
       "      <td>8 days ago</td>\n",
       "      <td>656MB</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ftakao2007/mail_centos7</td>\n",
       "      <td>virtual_for_jorurimail</td>\n",
       "      <td>35e0eebeeb79</td>\n",
       "      <td>8 days ago</td>\n",
       "      <td>657MB</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    REPOSITORY                      TAG       IMAGE ID  \\\n",
       "0  ftakao2007/mysql5.6_centos7                   latest   09a773ad8e34   \n",
       "1  ftakao2007/openldap_centos7                   latest   572b38993fa4   \n",
       "2      ftakao2007/mail_centos7                   latest   0733ee002c86   \n",
       "3      ftakao2007/mail_centos7   virtual_for_jorurimail   35e0eebeeb79   \n",
       "\n",
       "       CREATED     SIZE  \n",
       "0  12 days ago   2.16GB  \n",
       "1  10 days ago    596MB  \n",
       "2   8 days ago    656MB  \n",
       "3   8 days ago    657MB  "
      ]
     },
     "execution_count": 142,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "\n",
    "### コマンドの実行結果を取得する\n",
    "res = subprocess.run([\"docker\", \"images\"], stdout=subprocess.PIPE)\n",
    "\n",
    "### バイト列を文字列にする\n",
    "a = res.stdout.decode('utf-8')\n",
    "#print(a)\n",
    "\n",
    "### 改行を区切りに要素に入れる\n",
    "b = a.split(\"\\n\")\n",
    "#print(b)\n",
    "\n",
    "### それぞれの要素で半角空白2文字を区切り文字にし、空の要素は除く\n",
    "c = []\n",
    "for i in range(len(b)):\n",
    "    item = b[i]\n",
    "    if i != 0:\n",
    "        c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "#print(c)\n",
    "        \n",
    "### 列名をリストcolに入れる\n",
    "col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "\n",
    "### データをデータフレームに入れて出力する\n",
    "del c[0]\n",
    "df = pd.DataFrame(c, columns=col)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### コンテナリスト"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>CONTAINER ID</th>\n",
       "      <th>IMAGE</th>\n",
       "      <th>PORTS</th>\n",
       "      <th>NAMES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>c91b49b1012f</td>\n",
       "      <td>ftakao2007/mysql5.6_centos7:latest</td>\n",
       "      <td>0.0.0.0:3306-&gt;3306/tcp, 0.0.0.0:2222-&gt;22/tcp</td>\n",
       "      <td>mysql5.6_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>49914008c382</td>\n",
       "      <td>ftakao2007/openldap_centos7:latest</td>\n",
       "      <td>0.0.0.0:389-&gt;389/tcp, 0.0.0.0:2223-&gt;22/tcp</td>\n",
       "      <td>openldap_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>d8eb45ab72c7</td>\n",
       "      <td>ftakao2007/mail_centos7:latest</td>\n",
       "      <td>0.0.0.0:2224-&gt;22/tcp, 0.0.0.0:2525-&gt;25/tcp, 0.0.0.0:1143-&gt;143/tcp</td>\n",
       "      <td>mail_server_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01faf6b40cba</td>\n",
       "      <td>ftakao2007/mail_centos7:virtual_for_jorurimail</td>\n",
       "      <td>0.0.0.0:2225-&gt;22/tcp, 0.0.0.0:2526-&gt;25/tcp, 0.0.0.0:1144-&gt;143/tcp</td>\n",
       "      <td>mail_server_virtual_for_jorurimail_centos7</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   CONTAINER ID                                           IMAGE  \\\n",
       "0  c91b49b1012f              ftakao2007/mysql5.6_centos7:latest   \n",
       "1  49914008c382              ftakao2007/openldap_centos7:latest   \n",
       "2  d8eb45ab72c7                  ftakao2007/mail_centos7:latest   \n",
       "3  01faf6b40cba  ftakao2007/mail_centos7:virtual_for_jorurimail   \n",
       "\n",
       "                                                                PORTS  \\\n",
       "0                        0.0.0.0:3306->3306/tcp, 0.0.0.0:2222->22/tcp   \n",
       "1                          0.0.0.0:389->389/tcp, 0.0.0.0:2223->22/tcp   \n",
       "2   0.0.0.0:2224->22/tcp, 0.0.0.0:2525->25/tcp, 0.0.0.0:1143->143/tcp   \n",
       "3   0.0.0.0:2225->22/tcp, 0.0.0.0:2526->25/tcp, 0.0.0.0:1144->143/tcp   \n",
       "\n",
       "                                         NAMES  \n",
       "0                             mysql5.6_centos7  \n",
       "1                             openldap_centos7  \n",
       "2                          mail_server_centos7  \n",
       "3   mail_server_virtual_for_jorurimail_centos7  "
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "\n",
    "### コマンドの実行結果を取得する\n",
    "res = subprocess.run(['docker', 'ps', '-a'], stdout=subprocess.PIPE)\n",
    "\n",
    "### バイト列を文字列にする\n",
    "a = res.stdout.decode('utf-8')\n",
    "#print(a)\n",
    "\n",
    "### 改行を区切りに要素に入れる\n",
    "b = a.split(\"\\n\")\n",
    "#print(b)\n",
    "\n",
    "### それぞれの要素で半角空白2文字を区切り文字にし、空の要素は除く\n",
    "c = []\n",
    "for i in range(len(b)):\n",
    "    item = b[i]\n",
    "    if i != 0:\n",
    "        c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "#print(c)\n",
    "        \n",
    "### 列名をリストcolに入れる\n",
    "col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "\n",
    "### データをデータフレームに入れて出力する\n",
    "del c[0]\n",
    "df = pd.DataFrame(c, columns=col)\n",
    "df\n",
    "\n",
    "### COMMAND,CREATED,STATUS列を削除する\n",
    "del_command = []\n",
    "for i in c:\n",
    "    i_tmp = list(i)\n",
    "    del i_tmp[2:5]\n",
    "    del_command.append(tuple(i_tmp))\n",
    "del col[2:5]\n",
    "df = pd.DataFrame(del_command, columns=col)\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### コンテナリスト(複数ホスト指定可能, ユーザに必要な情報を表示、PORTSですべて表示)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>DOCKER HOST</th>\n",
       "      <th>PORTS</th>\n",
       "      <th>NAMES</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10.136.3.168</td>\n",
       "      <td>3306-&gt;3306/tcp, 2222-&gt;22/tcp</td>\n",
       "      <td>mysql5.6_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10.136.3.168</td>\n",
       "      <td>389-&gt;389/tcp, 2223-&gt;22/tcp</td>\n",
       "      <td>openldap_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10.136.3.168</td>\n",
       "      <td>2224-&gt;22/tcp, 2525-&gt;25/tcp, 1143-&gt;143/tcp</td>\n",
       "      <td>mail_server_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10.136.3.168</td>\n",
       "      <td>2225-&gt;22/tcp, 2526-&gt;25/tcp, 1144-&gt;143/tcp</td>\n",
       "      <td>mail_server_virtual_for_jorurimail_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10.136.3.180</td>\n",
       "      <td>3306/tcp, 2292-&gt;22/tcp, 8095-&gt;80/tcp</td>\n",
       "      <td>redmine2.6.10_centos7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10.136.3.180</td>\n",
       "      <td>5902-&gt;5902/tcp, 2244-&gt;22/tcp</td>\n",
       "      <td>gui_ubuntu16.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>10.136.3.180</td>\n",
       "      <td>3000-&gt;3000/tcp, 8888-&gt;8888/tcp, 2238-&gt;22/tcp</td>\n",
       "      <td>jupyter_iruby</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    DOCKER HOST                                          PORTS  \\\n",
       "0  10.136.3.168                   3306->3306/tcp, 2222->22/tcp   \n",
       "1  10.136.3.168                     389->389/tcp, 2223->22/tcp   \n",
       "2  10.136.3.168      2224->22/tcp, 2525->25/tcp, 1143->143/tcp   \n",
       "3  10.136.3.168      2225->22/tcp, 2526->25/tcp, 1144->143/tcp   \n",
       "4  10.136.3.180           3306/tcp, 2292->22/tcp, 8095->80/tcp   \n",
       "5  10.136.3.180                   5902->5902/tcp, 2244->22/tcp   \n",
       "6  10.136.3.180   3000->3000/tcp, 8888->8888/tcp, 2238->22/tcp   \n",
       "\n",
       "                                         NAMES  \n",
       "0                             mysql5.6_centos7  \n",
       "1                             openldap_centos7  \n",
       "2                          mail_server_centos7  \n",
       "3   mail_server_virtual_for_jorurimail_centos7  \n",
       "4                        redmine2.6.10_centos7  \n",
       "5                              gui_ubuntu16.04  \n",
       "6                                jupyter_iruby  "
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "pd.set_option(\"display.max_colwidth\", 100)\n",
    "\n",
    "### 全てのdockerhost\n",
    "dockerhost_list = ['10.136.3.168','10.136.3.180']\n",
    "\n",
    "result = []\n",
    "\n",
    "for j in range(len(dockerhost_list)):\n",
    "    ### コマンドの実行結果を取得する\n",
    "    res = subprocess.run(['ssh',dockerhost_list[j],'docker', 'ps'], stdout=subprocess.PIPE)\n",
    "\n",
    "    ### バイト列を文字列にする\n",
    "    a = res.stdout.decode('utf-8')\n",
    "    #print(a)\n",
    "\n",
    "    ### 改行を区切りに要素に入れる\n",
    "    b = a.split(\"\\n\")\n",
    "    #print(b)\n",
    "\n",
    "    ### それぞれの要素で半角空白2文字を区切り文字にし、空の要素は除く\n",
    "    c = []\n",
    "    for i in range(len(b)):\n",
    "        item = b[i]\n",
    "        if i != 0:\n",
    "            c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "\n",
    "    ### 列名の要素を削除\n",
    "    del c[0]\n",
    "    #print(c)\n",
    "\n",
    "    ### 列名をリストcolに入れる\n",
    "    col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "\n",
    "    ### CONTAINER ID, IMAGE, COMMAND,CREATED,STATUS列を削除し、DOCKERHOST列を追加する\n",
    "    for i in c:\n",
    "        i_tmp = list(i)\n",
    "        del i_tmp[0:5]\n",
    "        i_tmp.insert(0,dockerhost_list[j])\n",
    "        i_tmp = list(map(lambda x: x.replace('0.0.0.0:','') , i_tmp))\n",
    "        result.append(tuple(i_tmp))\n",
    "    del col[0:5]\n",
    "    col.insert(0,'DOCKER HOST')\n",
    "\n",
    "### 表示\n",
    "df = pd.DataFrame(result, columns=col)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CURDATE()\n",
      "2017-07-20\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "### mysql接続確認\n",
    "mysql -ujoruri -p'pass' -h 10.136.3.168 -P 3306 -e \"SELECT CURDATE();\" 2> /dev/null"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### MySQLに登録"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/ipykernel_launcher.py:13: Warning: (1007, \"Can't create database 'docker'; database exists\")\n",
      "  del sys.path[0]\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Field</th>\n",
       "      <th>Type</th>\n",
       "      <th>Null</th>\n",
       "      <th>Key</th>\n",
       "      <th>Default</th>\n",
       "      <th>Extra</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>docker_host</td>\n",
       "      <td>text</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ports</td>\n",
       "      <td>text</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>names</td>\n",
       "      <td>text</td>\n",
       "      <td>YES</td>\n",
       "      <td></td>\n",
       "      <td>None</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         Field  Type Null Key Default Extra\n",
       "0  docker_host  text  YES        None      \n",
       "1        ports  text  YES        None      \n",
       "2        names  text  YES        None      "
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import MySQLdb\n",
    "import pandas.io.sql as mysql\n",
    "\n",
    "db_name = \"docker\"\n",
    "table_name = \"containers\"\n",
    "\n",
    "### DB接続\n",
    "con = MySQLdb.connect(host='10.136.3.168', port=3306, user='jupyter', passwd='pass', charset='utf8')\n",
    "cursor = con.cursor()\n",
    "\n",
    "### 存在していなければdocker DBを作成\n",
    "sql = \"CREATE DATABASE IF NOT EXISTS {0};\".format(db_name)\n",
    "cursor.execute(sql)\n",
    "\n",
    "### テーブルの作成\n",
    "sql = \"\"\"\n",
    "CREATE TABLE IF NOT EXISTS {0}.{1}\n",
    "  (docker_host TEXT, ports TEXT, names TEXT);\n",
    "\"\"\".format(db_name, table_name)\n",
    "cursor.execute(sql)\n",
    "\n",
    "### テーブルの情報を取得\n",
    "sql = \"show columns from {0}.{1};\".format(db_name, table_name)\n",
    "df = mysql.read_sql(sql, con) \n",
    "\n",
    "### 接続を抜ける\n",
    "con.close()\n",
    "\n",
    "### テーブルの確認\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    DOCKER HOST                                          PORTS  \\\n",
      "0  10.136.3.168                   3306->3306/tcp, 2222->22/tcp   \n",
      "1  10.136.3.168                     389->389/tcp, 2223->22/tcp   \n",
      "2  10.136.3.168      2224->22/tcp, 2525->25/tcp, 1143->143/tcp   \n",
      "3  10.136.3.168      2225->22/tcp, 2526->25/tcp, 1144->143/tcp   \n",
      "4  10.136.3.180           3306/tcp, 2292->22/tcp, 8095->80/tcp   \n",
      "5  10.136.3.180                   5902->5902/tcp, 2244->22/tcp   \n",
      "6  10.136.3.180   3000->3000/tcp, 8888->8888/tcp, 2238->22/tcp   \n",
      "\n",
      "                                         NAMES  \n",
      "0                             mysql5.6_centos7  \n",
      "1                             openldap_centos7  \n",
      "2                          mail_server_centos7  \n",
      "3   mail_server_virtual_for_jorurimail_centos7  \n",
      "4                        redmine2.6.10_centos7  \n",
      "5                              gui_ubuntu16.04  \n",
      "6                                jupyter_iruby  \n"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "import sqlalchemy as sa\n",
    "import pandas.io.sql as mysql\n",
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "pd.set_option(\"display.max_colwidth\", 100)\n",
    "dockerhost_list = ['10.136.3.168','10.136.3.180']\n",
    "db_name = \"docker\"\n",
    "table_name = \"containers\"\n",
    "result = []\n",
    "\n",
    "### dockerhostの情報をdf_dockerに取得\n",
    "for j in range(len(dockerhost_list)):\n",
    "    res = subprocess.run(['ssh',dockerhost_list[j],'docker', 'ps'], stdout=subprocess.PIPE)\n",
    "    a = res.stdout.decode('utf-8')\n",
    "    b = a.split(\"\\n\")\n",
    "    c = []\n",
    "    for i in range(len(b)):\n",
    "        item = b[i]\n",
    "        if i != 0:\n",
    "            c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "    del c[0]\n",
    "    col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "    for i in c:\n",
    "        i_tmp = list(i)\n",
    "        del i_tmp[0:5]\n",
    "        i_tmp.insert(0,dockerhost_list[j])\n",
    "        i_tmp = list(map(lambda x: x.replace('0.0.0.0:','') , i_tmp))\n",
    "        result.append(tuple(i_tmp))\n",
    "    del col[0:5]\n",
    "    col.insert(0,'DOCKER HOST')\n",
    "df_docker = pd.DataFrame(result, columns=col)\n",
    "print(df_docker)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## (以下作業領域)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 141,
   "metadata": {},
   "outputs": [
    {
     "ename": "DatabaseError",
     "evalue": "Execution failed on sql 'SELECT * FROM zoo': (1146, \"Table 'test_db.zoo' doesn't exist\")",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mProgrammingError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1403\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1404\u001b[0;31m                 \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1405\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 250\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrorhandler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    251\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mdefaulterrorhandler\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m     49\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseException\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 50\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0merrorvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     51\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    246\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 247\u001b[0;31m             \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    410\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 411\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    412\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_post_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_do_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    373\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 374\u001b[0;31m         \u001b[0mdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    375\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query)\u001b[0m\n\u001b[1;32m    291\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 292\u001b[0;31m             \u001b[0m_mysql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    293\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mProgrammingError\u001b[0m: (1146, \"Table 'test_db.zoo' doesn't exist\")",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mDatabaseError\u001b[0m                             Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-141-859bb327eba1>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     47\u001b[0m \u001b[0mcon\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mMySQLdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnect\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdatabase\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'test_db'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mhost\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'10.136.3.168'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0muser\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'jupyter'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpasswd\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'pass'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcharset\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'utf8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     48\u001b[0m \u001b[0msql\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m\"SELECT * FROM zoo\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 49\u001b[0;31m \u001b[0mdf\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mmysql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcon\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     50\u001b[0m \u001b[0mdf\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     51\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_sql\u001b[0;34m(sql, con, index_col, coerce_float, params, parse_dates, columns, chunksize)\u001b[0m\n\u001b[1;32m    398\u001b[0m             \u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex_col\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_col\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparams\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    399\u001b[0m             \u001b[0mcoerce_float\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mcoerce_float\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparse_dates\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mparse_dates\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 400\u001b[0;31m             chunksize=chunksize)\n\u001b[0m\u001b[1;32m    401\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    402\u001b[0m     \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mread_query\u001b[0;34m(self, sql, index_col, coerce_float, params, parse_dates, chunksize)\u001b[0m\n\u001b[1;32m   1437\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1438\u001b[0m         \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0m_convert_params\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mparams\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1439\u001b[0;31m         \u001b[0mcursor\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1440\u001b[0m         \u001b[0mcolumns\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mcol_desc\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;32mfor\u001b[0m \u001b[0mcol_desc\u001b[0m \u001b[0;32min\u001b[0m \u001b[0mcursor\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdescription\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1441\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1414\u001b[0m             ex = DatabaseError(\n\u001b[1;32m   1415\u001b[0m                 \"Execution failed on sql '%s': %s\" % (args[0], exc))\n\u001b[0;32m-> 1416\u001b[0;31m             \u001b[0mraise_with_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mex\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1417\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1418\u001b[0m     \u001b[0;34m@\u001b[0m\u001b[0mstaticmethod\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/compat/__init__.py\u001b[0m in \u001b[0;36mraise_with_traceback\u001b[0;34m(exc, traceback)\u001b[0m\n\u001b[1;32m    342\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mtraceback\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0mEllipsis\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    343\u001b[0m             \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0m_\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtraceback\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 344\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mwith_traceback\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtraceback\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    345\u001b[0m \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    346\u001b[0m     \u001b[0;31m# this version of raise is a syntax error in Python 3\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1402\u001b[0m                 \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1403\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1404\u001b[0;31m                 \u001b[0mcur\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1405\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mcur\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1406\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 250\u001b[0;31m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merrorhandler\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    251\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    252\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_defer_warnings\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mdefaulterrorhandler\u001b[0;34m(***failed resolving arguments***)\u001b[0m\n\u001b[1;32m     48\u001b[0m     \u001b[0;32mdel\u001b[0m \u001b[0mconnection\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     49\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mBaseException\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 50\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0merrorvalue\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     51\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0merrorclass\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     52\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0merrorclass\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0merrorvalue\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, query, args)\u001b[0m\n\u001b[1;32m    245\u001b[0m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    246\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 247\u001b[0;31m             \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    248\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0mException\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    249\u001b[0m             \u001b[0mexc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msys\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexc_info\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;36m2\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    409\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    410\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 411\u001b[0;31m         \u001b[0mrowcount\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    412\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_post_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    413\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/cursors.py\u001b[0m in \u001b[0;36m_do_query\u001b[0;34m(self, q)\u001b[0m\n\u001b[1;32m    372\u001b[0m         \u001b[0mdb\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_get_db\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    373\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_last_executed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mq\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 374\u001b[0;31m         \u001b[0mdb\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mq\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    375\u001b[0m         \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_do_get_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    376\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrowcount\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/MySQLdb/connections.py\u001b[0m in \u001b[0;36mquery\u001b[0;34m(self, query)\u001b[0m\n\u001b[1;32m    290\u001b[0m             \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_query_result\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    291\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 292\u001b[0;31m             \u001b[0m_mysql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mconnection\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    293\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    294\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m__enter__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mDatabaseError\u001b[0m: Execution failed on sql 'SELECT * FROM zoo': (1146, \"Table 'test_db.zoo' doesn't exist\")"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "#import sqlalchemy as sa\n",
    "from sqlalchemy import create_engine\n",
    "import pandas.io.sql as mysql\n",
    "import pandas as pd\n",
    "\n",
    "\n",
    "# engine = create_engine(\"mysql+mysqldb://jupyter:pass@10.136.3.168/test_db?charset=utf8\")\n",
    "# rows = engine.execute('SELECT * FROM zoo')\n",
    "# for row in rows:\n",
    "#    print(row)\n",
    "\n",
    "\n",
    "# engine = create_engine(\"mysql+mysqldb://jupyter:pass@10.136.3.168/test_db?charset=utf8\")\n",
    "# connection = engine.connect()\n",
    "# data = pd.read_sql_table('zoo', connection)\n",
    "\n",
    "# result = connection.execute(\"select username from users\")\n",
    "# for row in result:\n",
    "#     print(\"username:\", row['username'])\n",
    "# connection.close()\n",
    "#with engine.connect() as conn, conn.begin():\n",
    "#    data = pd.read_sql_table('data', conn)\n",
    "#data = mysql.read_sql_table('test_db', con)\n",
    "\n",
    "\n",
    "### OK\n",
    "# engine = create_engine(\"mysql+mysqldb://jupyter:pass@10.136.3.168/test_db?charset=utf8\")\n",
    "# con = engine.connect()\n",
    "# result = con.execute(\"select * from zoo\")\n",
    "# for row in result:\n",
    "#     print(\"critter\", row['critter'])\n",
    "# con.close()\n",
    "\n",
    "\n",
    "\n",
    "# engine = create_engine(\"mysql+mysqldb://jupyter:pass@10.136.3.168/test_db?charset=utf8\")\n",
    "# con = engine.connect()\n",
    "# df = pd.DataFrame([('うし',5,1500)], columns=['critter', 'count', 'damages'])\n",
    "# df.to_sql('zoo', con, if_exists='replace')\n",
    "#df.to_sql('zoo', con, if_exists='replace', chunksize=1000, dtype={'critter': sqlalchemy.types.String, 'count': Int, 'count': Int})\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "### OK\n",
    "con = MySQLdb.connect(database='test_db', host='10.136.3.168', user='jupyter', passwd='pass', charset='utf8')\n",
    "sql = \"SELECT * FROM zoo\"\n",
    "df = mysql.read_sql(sql, con) \n",
    "df\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "#rows = engine.execute('SELECT * FROM zoo')\n",
    "\n",
    "#df = pd.DataFrame([('うし',5,1500)], columns=['critter', 'count', 'damages'])\n",
    "#df\n",
    "\n",
    "#df.to_sql('zoo', engine)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/core/generic.py:1362: UserWarning: The spaces in these column names will not be changed. In pandas versions < 0.14, spaces were converted to underscores.\n",
      "  chunksize=chunksize, dtype=dtype)\n"
     ]
    },
    {
     "ename": "AttributeError",
     "evalue": "'Engine' object has no attribute 'cursor'",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-98-732001cc64d5>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m     56\u001b[0m \u001b[0;31m#engine = sa.create_engine(url, echo=True)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     57\u001b[0m \u001b[0mengine\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msa\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate_engine\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0murl\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 58\u001b[0;31m \u001b[0mdf_docker\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtable_name\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mengine\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'append'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mFalse\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     59\u001b[0m \u001b[0;31m#df_docker.to_sql(table_name, engine, flavor='mysql', if_exists='replace', index=False)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m     60\u001b[0m \u001b[0;31m#df_docker.to_sql(table_name, con=engine, if_exists='replace')\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/core/generic.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1360\u001b[0m         sql.to_sql(self, name, con, flavor=flavor, schema=schema,\n\u001b[1;32m   1361\u001b[0m                    \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mif_exists\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex_label\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_label\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1362\u001b[0;31m                    chunksize=chunksize, dtype=dtype)\n\u001b[0m\u001b[1;32m   1363\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1364\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mto_pickle\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mpath\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompression\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m'infer'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(frame, name, con, flavor, schema, if_exists, index, index_label, chunksize, dtype)\u001b[0m\n\u001b[1;32m    469\u001b[0m     pandas_sql.to_sql(frame, name, if_exists=if_exists, index=index,\n\u001b[1;32m    470\u001b[0m                       \u001b[0mindex_label\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_label\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 471\u001b[0;31m                       chunksize=chunksize, dtype=dtype)\n\u001b[0m\u001b[1;32m    472\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    473\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mto_sql\u001b[0;34m(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype)\u001b[0m\n\u001b[1;32m   1503\u001b[0m                             \u001b[0mif_exists\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mif_exists\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex_label\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mindex_label\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1504\u001b[0m                             dtype=dtype)\n\u001b[0;32m-> 1505\u001b[0;31m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1506\u001b[0m         \u001b[0mtable\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mchunksize\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1507\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mcreate\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    584\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    585\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mcreate\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 586\u001b[0;31m         \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexists\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    587\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mif_exists\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;34m'fail'\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    588\u001b[0m                 \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Table '%s' already exists.\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexists\u001b[0;34m(self)\u001b[0m\n\u001b[1;32m    572\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    573\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mexists\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 574\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpd_sql\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mhas_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mschema\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    575\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    576\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0msql_schema\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mhas_table\u001b[0;34m(self, name, schema)\u001b[0m\n\u001b[1;32m   1515\u001b[0m                  \"WHERE type='table' AND name=%s;\") % wld\n\u001b[1;32m   1516\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1517\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m>\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1518\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1519\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mget_table\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable_name\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mschema\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;32mNone\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m/usr/local/pyenv/versions/3.6.1/envs/jupyter/lib/python3.6/site-packages/pandas/io/sql.py\u001b[0m in \u001b[0;36mexecute\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m   1397\u001b[0m             \u001b[0mcur\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcon\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1398\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1399\u001b[0;31m             \u001b[0mcur\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcon\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcursor\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1400\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1401\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mkwargs\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mAttributeError\u001b[0m: 'Engine' object has no attribute 'cursor'"
     ]
    }
   ],
   "source": [
    "import MySQLdb\n",
    "import sqlalchemy as sa\n",
    "import pandas.io.sql as mysql\n",
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "pd.set_option(\"display.max_colwidth\", 100)\n",
    "dockerhost_list = ['10.136.3.168','10.136.3.180']\n",
    "db_name = \"docker\"\n",
    "table_name = \"containers\"\n",
    "result = []\n",
    "\n",
    "### dockerhostの情報をdf_dockerに取得\n",
    "for j in range(len(dockerhost_list)):\n",
    "    res = subprocess.run(['ssh',dockerhost_list[j],'docker', 'ps'], stdout=subprocess.PIPE)\n",
    "    a = res.stdout.decode('utf-8')\n",
    "    b = a.split(\"\\n\")\n",
    "    c = []\n",
    "    for i in range(len(b)):\n",
    "        item = b[i]\n",
    "        if i != 0:\n",
    "            c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "    del c[0]\n",
    "    col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "    for i in c:\n",
    "        i_tmp = list(i)\n",
    "        del i_tmp[0:5]\n",
    "        i_tmp.insert(0,dockerhost_list[j])\n",
    "        i_tmp = list(map(lambda x: x.replace('0.0.0.0:','') , i_tmp))\n",
    "        result.append(tuple(i_tmp))\n",
    "    del col[0:5]\n",
    "    col.insert(0,'DOCKER HOST')\n",
    "df_docker = pd.DataFrame(result, columns=col)\n",
    "#df_docker\n",
    "\n",
    "\n",
    "# ### DB接続\n",
    "# con = MySQLdb.connect(database=db_name, host='10.136.3.168', port=3306, user='jupyter', passwd='pass', charset='utf8')\n",
    "\n",
    "# ### データフレームを元にテーブルを作成(テーブルがあれば再作成)\n",
    "# df_docker.to_sql(table_name, engine.raw_connection(), flavor='mysql', if_exists='append')\n",
    "# #df_docker.to_sql(name=table_name, con=con, flavor='mysql', if_exists='replace')\n",
    "\n",
    "# #mysql.write_frame(df_docker, con=con, name=table_name, if_exists='replace', flavor='mysql')\n",
    "\n",
    "# ### 接続を抜ける\n",
    "# con.close()\n",
    "\n",
    "\n",
    "#engine = create_engine(\"mysql+mysqldb://USER:\"+'PASSWORD'+\"@localhost/DATABASE\")\n",
    "#df.to_sql(con=engine, if_exists='append', index=False)\n",
    "\n",
    "#url = 'mysql+mysqldb://jupyter:pass@10.136.3.168/{0}?charset=utf8'.format(db_name)\n",
    "#url = 'mysql+mysqldb://jupyter:pass@10.136.3.168/{0}?charset=utf8'.format(db_name)\n",
    "url = 'mysql+mysqldb://jupyter:pass@10.136.3.168/{0}?charset=utf8'.format(db_name)\n",
    "#engine = sa.create_engine(url, echo=True)\n",
    "engine = sa.create_engine(url)\n",
    "df_docker.to_sql(table_name, engine, if_exists='append', index=False)\n",
    "#df_docker.to_sql(table_name, engine, flavor='mysql', if_exists='replace', index=False)\n",
    "#df_docker.to_sql(table_name, con=engine, if_exists='replace')\n",
    "#df_docker.to_sql(table_name, engine, if_exists='replace')\n",
    "\n",
    "# engine.execute('DROP TABLE zoo')\n",
    "# engine.execute('CREATE TABLE zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')\n",
    "# #engine.execute('CREATE TABLE IF NOT EXISTS zoo (critter VARCHAR(20) PRIMARY KEY, count INT, damages FLOAT)')\n",
    "\n",
    "# # SQL文に「?」が使用できないので、代わりに「%s」を使用\n",
    "# ins = \"INSERT INTO zoo (critter, count, damages) VALUES (%s, %s, %s)\"\n",
    "# engine.execute(ins, \"あひる\", 10, 0.0)\n",
    "# engine.execute(ins, \"くま\", 2, 1000.0)\n",
    "# engine.execute(ins, \"いたち\", 1, 2000.0)\n",
    "\n",
    "# rows = engine.execute('SELECT * FROM zoo')\n",
    "\n",
    "# for row in rows:\n",
    "#     print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b'CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS              PORTS                                                               NAMES\\n01faf6b40cba        ftakao2007/mail_centos7:virtual_for_jorurimail   \"sh -c \\'exec /usr/...\"   2 days ago          Up 2 days           0.0.0.0:2225->22/tcp, 0.0.0.0:2526->25/tcp, 0.0.0.0:1144->143/tcp   mail_server_virtual_for_jorurimail_centos7\\nd8eb45ab72c7        ftakao2007/mail_centos7:latest                   \"sh -c \\'exec /usr/...\"   2 days ago          Up 2 days           0.0.0.0:2224->22/tcp, 0.0.0.0:2525->25/tcp, 0.0.0.0:1143->143/tcp   mail_server_centos7\\n49914008c382        ftakao2007/openldap_centos7:latest               \"sh -c \\'exec /usr/...\"   2 days ago          Up 2 days           0.0.0.0:389->389/tcp, 0.0.0.0:2223->22/tcp                          openldap_centos7\\nc91b49b1012f        ftakao2007/mysql5.6_centos7:latest               \"sh -c \\'exec /usr/...\"   2 days ago          Up 2 days           0.0.0.0:3306->3306/tcp, 0.0.0.0:2222->22/tcp                        mysql5.6_centos7\\n'\n"
     ]
    }
   ],
   "source": [
    "import subprocess\n",
    "args = ['docker', 'ps', '-a']\n",
    "try:\n",
    "    res = subprocess.check_output(args)\n",
    "    print(res)\n",
    "except:\n",
    "    print(\"Error.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "REPOSITORY                    TAG                      IMAGE ID            CREATED             SIZE\n",
      "ftakao2007/mail_centos7       virtual_for_jorurimail   35e0eebeeb79        18 hours ago        657MB\n",
      "ftakao2007/mail_centos7       latest                   0733ee002c86        19 hours ago        656MB\n",
      "ftakao2007/openldap_centos7   latest                   572b38993fa4        2 days ago          596MB\n",
      "ftakao2007/mysql5.6_centos7   latest                   09a773ad8e34        4 days ago          2.16GB\n",
      "ftakao2007/centos7            latest                   0fede72fa1fe        4 days ago          488MB\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "docker images"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CONTAINER ID        IMAGE                                            COMMAND                  CREATED             STATUS              PORTS                                                               NAMES\n",
      "d466248a93d9        ftakao2007/mail_centos7:virtual_for_jorurimail   \"sh -c 'exec /usr/...\"   6 minutes ago       Up 6 minutes        0.0.0.0:2225->22/tcp, 0.0.0.0:2526->25/tcp, 0.0.0.0:1144->143/tcp   mail_server_virtual_for_jorurimail_centos7\n",
      "8784dfcb21c3        ftakao2007/mail_centos7:latest                   \"sh -c 'exec /usr/...\"   22 minutes ago      Up 22 minutes       0.0.0.0:2224->22/tcp, 0.0.0.0:2525->25/tcp, 0.0.0.0:1143->143/tcp   mail_server_centos7\n",
      "3bc5d977b203        ftakao2007/openldap_centos7:latest               \"sh -c 'exec /usr/...\"   26 minutes ago      Up 26 minutes       0.0.0.0:389->389/tcp, 0.0.0.0:2223->22/tcp                          openldap_centos7\n",
      "4c0ae849ad6a        ftakao2007/mysql5.6_centos7:latest               \"sh -c 'exec /usr/...\"   29 minutes ago      Up 29 minutes       0.0.0.0:3306->3306/tcp, 0.0.0.0:2222->22/tcp                        mysql5.6_centos7\n"
     ]
    }
   ],
   "source": [
    "%%bash\n",
    "docker ps -a"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import subprocess\n",
    "import sys\n",
    "\n",
    "### コマンドの実行結果を取得する\n",
    "res = subprocess.run([\"docker\", \"images\"], stdout=subprocess.PIPE)\n",
    "\n",
    "### バイト列を文字列にする\n",
    "a = res.stdout.decode('utf-8')\n",
    "#print(a)\n",
    "\n",
    "### 改行を区切りに要素に入れる\n",
    "b = a.split(\"\\n\")\n",
    "#print(b)\n",
    "\n",
    "### それぞれの要素で半角空白2文字を区切り文字にし、空の要素は除く\n",
    "c = []\n",
    "for i in range(len(b)):\n",
    "    item = b[i]\n",
    "    if i != 0:\n",
    "        c.insert(0,tuple(filter(lambda x: x != '', item.split(\"  \"))))\n",
    "#print(c)\n",
    "\n",
    "pd_index = []\n",
    "for i in c[1:]:\n",
    "    pd_index.append(i[2])\n",
    "print(pd_index)\n",
    "        \n",
    "### 列名をリストcolに入れる\n",
    "col = list(filter(lambda x: x != '', b[0].split(\"  \")))\n",
    "\n",
    "### データをデータフレームに入れて出力する\n",
    "df = pd.DataFrame(c[1:], columns=col, index=pd_index)\n",
    "df"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
